from dao.basedao import BaseDao


class HouseDao(BaseDao):

    # 使用SQL语句来统计分析数据：聚合函数
    def statisticHouseAvgPrice(self):
        sql = 'select city_name, avg(mean_price) as price from lianjia_house_info group by city_name order by price DESC ;'
        self.execute(sql, ret='dict')
        data = self.fetchall()
        return data

    # def statisticContrast(self):
    #     sql = 'select city_name, avg(rent_money) as price from lianjia_house_info group by city_name order by avg(rent_money) DESC ;'

    def statsticInfrastuctureVersusPriceLT30(self):
        sql = 'select ' \
              'avg(restaurant) + avg(supermarket)+avg(market) as live, ' \
              'avg(mid_school)+avg(primary_school)+avg(nursery)+avg(collage)as school,' \
              'avg(holiday_resort)+avg(amusement)+avg(theatre)+avg(park) as amuse,' \
              'avg(hospital)as hospital,avg(bus)+avg(subway)+avg(rail)+avg(train)+avg(airport)+avg(parking)as traffic ' \
              'from lianjia_house_info where mean_price<30';
        self.execute(sql)
        data = self.fetchall()
        data = list(data[0])
        for i in range(len(data)):
            data[i] = float(data[i])
        data = {'name':'<30元/m²','value':data}
        return data

    def statsticInfrastuctureVersusPrice30_60(self):
        sql = 'select ' \
              'avg(restaurant) + avg(supermarket)+avg(market) as live, ' \
              'avg(mid_school)+avg(primary_school)+avg(nursery)+avg(collage)as school,' \
              'avg(holiday_resort)+avg(amusement)+avg(theatre)+avg(park) as amuse,' \
              'avg(hospital)as hospital,avg(bus)+avg(subway)+avg(rail)+avg(train)+avg(airport)+avg(parking)as traffic ' \
              'from lianjia_house_info where mean_price>=30 and mean_price<60';
        self.execute(sql)
        data = self.fetchall()
        data = list(data[0])
        for i in range(len(data)):
            data[i] = float(data[i])
        data = {'name':'30-60元/m²','value':data}
        return data

    def statsticInfrastuctureVersusPrice60_90(self):
        sql = 'select ' \
              'avg(restaurant) + avg(supermarket)+avg(market) as live, ' \
              'avg(mid_school)+avg(primary_school)+avg(nursery)+avg(collage)as school,' \
              'avg(holiday_resort)+avg(amusement)+avg(theatre)+avg(park) as amuse,' \
              'avg(hospital)as hospital,avg(bus)+avg(subway)+avg(rail)+avg(train)+avg(airport)+avg(parking)as traffic ' \
              'from lianjia_house_info where mean_price>=60 and mean_price<90';
        self.execute(sql)
        data = self.fetchall()
        data = list(data[0])
        for i in range(len(data)):
            data[i] = float(data[i])
        data = {'name':'60-90元/m²','value':data}

        return data

    def statsticInfrastuctureVersusPrice90_120(self):
        sql = 'select ' \
              'avg(restaurant) + avg(supermarket)+avg(market) as live, ' \
              'avg(mid_school)+avg(primary_school)+avg(nursery)+avg(collage)as school,' \
              'avg(holiday_resort)+avg(amusement)+avg(theatre)+avg(park) as amuse,' \
              'avg(hospital)as hospital,avg(bus)+avg(subway)+avg(rail)+avg(train)+avg(airport)+avg(parking)as traffic ' \
              'from lianjia_house_info where mean_price>=90 and mean_price<120';
        self.execute(sql)
        data = self.fetchall()
        data = list(data[0])
        for i in range(len(data)):
            data[i] = float(data[i])
        data = {'name':'90-120元/m²','value':data}

        return data

    def statsticInfrastuctureVersusPriceGT120(self):
        sql = 'select ' \
              'avg(restaurant) + avg(supermarket)+avg(market) as live, ' \
              'avg(mid_school)+avg(primary_school)+avg(nursery)+avg(collage)as school,' \
              'avg(holiday_resort)+avg(amusement)+avg(theatre)+avg(park) as amuse,' \
              'avg(hospital)as hospital,avg(bus)+avg(subway)+avg(rail)+avg(train)+avg(airport)+avg(parking)as traffic ' \
              'from lianjia_house_info where mean_price>=120';
        self.execute(sql)
        data = self.fetchall()
        data = list(data[0])
        for i in range(len(data)):
            data[i] = float(data[i])
        data = {'name':'>120元/m²','value':data}

        return data

    def statisticHouseArea(self):
        sql = 'select area,count(*)as number from house_info.lianjia_house_info where area>0 group by area'
        self.execute(sql,ret='dict')
        data = self.fetchall()
        return data;
    # def statisticJobTypeJobCount(self):
    #     sql = "select count(*) as s, jobtype from t_jobdata group by jobtype"
    #     self.execute(sql, ret='dict')
    #     return self.fetchall()
    #     pass
    #
    # def statisticCityJobCount(self):
    #     sql = "select count(*) as value, jobcity as name from t_jobdata group by jobcity"
    #     self.execute(sql, ret='dict')
    #     return self.fetchall()
    #     pass

    pass
